package com.kcsj.dao;

import com.kcsj.domain.Inventory;
import com.kcsj.domain.ProductModel;
import com.kcsj.domain.Warehouse;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @author 傅立贤
 * 30th Nov. 2020
 */
public class InventoryDao {
    public static InventoryDao inventoryDao = new InventoryDao();

    private InventoryDao() {
    }

    public static InventoryDao getInstance() {
        return inventoryDao;
    }

    public boolean add(Inventory inventory, Connection connection) throws SQLException {
        String sql = "INSERT INTO inventory (warehouseNo ,productModelNo ,number)"+
                " values (?,?,?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1,inventory.getWarehouse().getWarehouseNo());
        pstmt.setString(2,inventory.getProductModel().getProductModelNo());
        pstmt.setInt(3,inventory.getAmount());
        int rowsAffected = pstmt.executeUpdate();
        return rowsAffected>0;
    }


    //查找库存 by姜瑞临
    public Collection<Inventory> find(String warehouseNo, Connection conn) throws SQLException {
        Collection<Inventory> inventories = new LinkedHashSet<>();
        String sql = "select * from inventory where warehouseNo = " + warehouseNo;
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(sql);
        //执行SQL语句
        ResultSet results = statement.executeQuery();
        //遍历resultSet，并将结果写入对象存进集合内
        while (results.next()){
            Warehouse warehouse = WarehouseDao.getInstance().find(results.getString("warehouseNo"),conn);
            ProductModel productModel = ProductModelDao.productModelDao.find(results.getString("productModelNo"),conn);
            Integer amount = results.getInt("number");
            Inventory inventory = new Inventory(warehouse,productModel,amount);
            inventories.add(inventory);
        }
        //关闭资源
        JdbcHelper.close(results,statement,null);
        return inventories;
    }


    //查找库存 by姜瑞临
    public Collection<Warehouse> findByProductNo(Connection conn, String productNo) throws SQLException {
        ArrayList<Warehouse> warehouses = new ArrayList<>();
        String sql = "select * from inventory where productModelNo = ?";
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1,productNo);
        //执行SQL语句
        ResultSet results = statement.executeQuery();
        //遍历resultSet，并将结果写入对象存进集合内
        while (results.next()){
            Warehouse warehouse = WarehouseDao.getInstance().find(results.getString("warehouseNo"),conn);
            warehouses.add(warehouse);
        }
        //关闭资源
        JdbcHelper.close(results,statement,null);
        return warehouses;
    }

    //查找库存 by姜瑞临
    public Collection<Inventory> findAll(Connection conn, List<Condition> conditionList) throws SQLException {
        Collection<Inventory> inventories = new HashSet<>();
        StringBuilder sql = new StringBuilder("select * from inventory ");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql.append(whereClause);
        }
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(sql.toString());
        //执行SQL语句
        ResultSet results = statement.executeQuery();
        //遍历resultSet，并将结果写入对象存进集合内
        while (results.next()){
            Warehouse warehouse = WarehouseDao.getInstance().find(results.getString("warehouseNo"),conn);
            ProductModel productModel = ProductModelDao.productModelDao.find(results.getString("productModelNo"),conn);
            Integer amount = results.getInt("number");
            Inventory inventory = new Inventory(warehouse,productModel,amount);
            inventories.add(inventory);
        }
        //关闭资源
        JdbcHelper.close(results,statement,null);
        return inventories;
    }

    //更新库存
    public boolean update(Inventory inventory, Connection connection) throws SQLException {
        String sql;
        if (inventory.getAmount()>=0) {
            sql = "UPDATE inventory SET number = number+" + 1 + " WHERE warehouseNo=? AND productModelNo=?";
        }else{
            sql = "UPDATE inventory SET number = number-" + 1 + " WHERE warehouseNo=? AND productModelNo=?";
        }
        System.out.println(inventory);
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1,inventory.getWarehouse().getWarehouseNo());
        pstmt.setString(2,inventory.getProductModel().getProductModelNo());
        int rowsAffected = pstmt.executeUpdate();
        return rowsAffected>0;
    }

}
